package proj1.oracle;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import proj1.constants.C.EXIT_STATUS;
import proj1.util.Logger;
import proj1.util.StringUtil;

/**
 * <pre>
 * Singleton connection to an Oracle database. Database properties are loaded from 'orcl.properties' file.
 * This class is immutable and thread safe. 
 * </pre>
 * 
 * @author Jason Campos
 */
public class OracleDB {
	static Logger log = Logger.getLogger(OracleDB.class);
	
	// Oracle DB Connection -- Singleton
	private static OracleDB database;
	
	/**
	 * <pre>
	 * Fetches or creates the connection to the Oracle database.
	 * </pre>
	 * @return The OracleDB object interfacing the database.
	 */
    public static OracleDB getInstance(){
    	// Synchronize on the class for thread safety to ensure only one
    	// connection is created.
    	synchronized(OracleDB.class){
    		if (database == null)
    			database = new OracleDB();
    	}
    	return database;
    }
    
    // INSTANCE VARIABLES
	// Database connection variables loaded from properties
    private final String DB_DRIVER;
    private final String DB_HOST;
    private final String DB_PORT;
    private final String DB_USER;
    private final String DB_PASSWORD;
    private final String DB_NAME;
    private Connection connection;
    
    /**
     * <pre>
     * OracleDB constructor. Creates a new connection to an Oracle database using the properties found in orcl.properties.
     * If unable to connect, the program will exit.
     * <pre>
     */
	private OracleDB(){
		Properties props = new Properties();
		InputStream in = null; 
		
		// Load the properties file variables
		// Make sure input stream gets closed properly
		try {
			in = getClass().getResourceAsStream("orcl.properties");
			props.load(in);
		} catch (IOException e) {
			log.error("Error loading properties file.", e);
			System.exit(EXIT_STATUS.FATAL);
		} finally {
			if(in != null){
				try {
					in.close();
				} catch (IOException e) {
					// silently fail
				}
			}
		}
		
		// Set the properties. If a value is not set, exit with invalid configuration
		DB_DRIVER = props.getProperty("orcl.driver");
		DB_HOST = props.getProperty("orcl.host");
		DB_PORT = props.getProperty("orcl.port");
		DB_USER = props.getProperty("orcl.username");
		DB_PASSWORD = props.getProperty("orcl.password");
		DB_NAME = props.getProperty("orcl.name");
		
		// Verify that all are set
		boolean validConfig = true;
		if(StringUtil.isNullOrEmpty(DB_DRIVER)){
			log.error("orcl.driver is not set in orcl.properties");
			validConfig = false;
		}
		
		if(StringUtil.isNullOrEmpty(DB_HOST)){
			log.error("orcl.dbhost is not set in orcl.properties");
			validConfig = false;
		}

		if(StringUtil.isNullOrEmpty(DB_PORT)){
			log.error("orcl.port is not set in orcl.properties");
			validConfig = false;
		}
		
		if(StringUtil.isNullOrEmpty(DB_USER)){
			log.error("orcl.username is not set in orcl.properties");
			validConfig = false;
		}
		
		if(StringUtil.isNullOrEmpty(DB_PASSWORD)){
			log.error("orcl.password is not set in orcl.properties");
			validConfig = false;
		}
		
		if(StringUtil.isNullOrEmpty(DB_NAME)){
			log.error("orcl.dbname is not set in orcl.properties");
			validConfig = false;
		}
		
		if(!validConfig)
			System.exit(EXIT_STATUS.INVALID_CONFIGURATION);
		
		// Config properties are set. Try to establish connection
        try {
            Class.forName(DB_DRIVER);
            String dbUrl = "jdbc:oracle:thin:@" + DB_HOST + ":" + DB_PORT + ":" + DB_NAME;
            connection = DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
        } catch (SQLException e) {
        	log.error("A SQL Exception has occurred", e);
        	System.exit(EXIT_STATUS.FATAL);
        } catch (ClassNotFoundException e){
            log.error("Could not load class " + DB_DRIVER, e);
            System.exit(EXIT_STATUS.INVALID_CONFIGURATION);
        }
        
        // Connected!
        log.info("Connection to database successful!");
	}
	
	@Override
	public void finalize(){
		if(connection != null){
			try {
				log.info("Disconnecting from Oracle...");
				connection.close();
				log.info("Disconnected.");
			} catch (SQLException e){
				// Ignore finalize stacktrace
			}
		}
	}
	
	// PUBLIC METHODS - DATABASE INTERACTION
	/**
	 * <pre>
	 * Executes an update to the database. Returns the number 
	 * of rows affected by the query.
	 * </pre>
	 * @param sqlQuery The SQL query to execute
	 * @return The number of affected database rows
	 */
	public int executeUpdate(String sqlQuery){
		// log the incoming query
		log.info(sqlQuery);
		int res = 0;
        try {
            Statement stmnt = connection.createStatement();
            res = stmnt.executeUpdate(sqlQuery);
            stmnt.close();
        } catch (SQLException e) {
        	log.error("An error occurred while executing query. " + e.getMessage(), e);
        }

        return res;
	}
	
	/**
	 * <pre>
	 * Executes a query against the database. Returns the {@link java.sql.ResultSet ResultSet}
	 * </pre>
	 * @String sqlQuery The SQL query to execute
	 * @return The result set of the query
	 */
	public ResultSet executeQuery(String sqlQuery){
		// log the incoming query
		log.info(sqlQuery);
		ResultSet rs = null;
        try {
            Statement stmnt = connection.createStatement();
            rs = stmnt.executeQuery(sqlQuery);
        } catch (SQLException e) {
        	log.error("An error occurred while executing query. " + e.getMessage(), e);
        }
        return rs;
	}
}
